﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Xml;
using DTOFrameworks;
using FooFun.DataElements.Dtos;

namespace PhysicalDataProvider
{
    public class DataService
    {
        DataSet ds;

        public static DataService GetData()
        {
            return new DataService();
        }

        private DataService()
        {
            ds = new DataSet();
        }

        const string NAME = "AccessProvider";

        public static DataTable FillTable(string sql)
        {

            string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

            DbProviderFactory factory
                = DbProviderFactories.GetFactory(providerName);
            DbDataAdapter da = factory.CreateDataAdapter();
            da.SelectCommand =
                factory.CreateCommand();
            da.SelectCommand.CommandText
                = sql;
            da.SelectCommand.Connection
                = factory.CreateConnection();
            da.SelectCommand.Connection.ConnectionString
                = ConfigurationManager
                .ConnectionStrings[NAME].ConnectionString;
            //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\FooFun\DataBase\FAQ.mdb";

            DataTable dt = new DataTable();

            da.Fill(dt);

            return dt;
        }

        public static int SaveTopic(IDataElement topic)
        {
            string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

            XmlElementDataElement de
                = topic as XmlElementDataElement;

            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            settings.Encoding = Encoding.UTF8;

            StringBuilder sb = new StringBuilder();
            XmlWriter xmlWriter = XmlWriter.Create(sb);

            de.Document.Save(xmlWriter);

            xmlWriter.Close();

            string content = sb.ToString();

            DbProviderFactory factory
               = DbProviderFactories.GetFactory(providerName);
            DbParameter parameter1 = factory.CreateParameter();
            parameter1.Value
                = topic["Title/Content"].Value.ToString();

            DbParameter parameter2 = factory.CreateParameter();
            parameter2.Value = content;

            DbParameter parameter3 = factory.CreateParameter();
            parameter3.Value = Convert.ToInt32(topic["@ForumId"]);

            TopicStatus status = TopicStatus.Asked;
            if (!topic["@status"].IsEmpty
                && topic["@status"].Equals("sealed"))
            {
                status = TopicStatus.Sealed;
            }

            DbParameter parameter4 = factory.CreateParameter();
            parameter4.Value = (int)status;

            using (DbConnection connection = SqlHelper.Connection)
            {
                connection.Open();
                DbCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"INSERT INTO t_Topics(Title,Content,ForumId,Status)
            VALUES(?,?,?,?)";
                cmd.Parameters.Add(parameter1);
                cmd.Parameters.Add(parameter2);
                cmd.Parameters.Add(parameter3);
                cmd.Parameters.Add(parameter4);

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY";
                int id = (int)cmd.ExecuteScalar();

                topic["@Id"].Value = id;

                DataService.Update(topic);

                connection.Close();

                return id;
            }
        }

        public static void Update(IDataElement topic)
        {
            string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

            XmlElementDataElement de
                = topic as XmlElementDataElement;

            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;

            StringBuilder sb = new StringBuilder();
            XmlWriter xmlWriter = XmlWriter.Create(sb);

            de.Document.Save(xmlWriter);

            xmlWriter.Close();

            string content = sb.ToString();

            DbProviderFactory factory
               = DbProviderFactories.GetFactory(providerName);
            DbParameter parameter1 = factory.CreateParameter();
            parameter1.Value = content;

            DbParameter parameter2 = factory.CreateParameter();
            parameter2.Value = Convert.ToInt32(topic["@ForumId"]);

            TopicStatus status = TopicStatus.Asked;
            if (!topic["@status"].IsEmpty
                && topic["@status"].Equals("sealed"))
            {
                status = TopicStatus.Sealed;
            }

            DbParameter parameter3 = factory.CreateParameter();
            parameter3.Value = (int)status;

            DbParameter parameter4 = factory.CreateParameter();
            parameter4.Value = Convert.ToInt32(topic["@Id"]);

            SqlHelper.ExecuteNonQuery(
                @"UPDATE t_Topics 
SET Content = ?,ForumId=?,Status=?
WHERE TopicId = ?", parameter1, parameter2, parameter3, parameter4);

        }

        public static Event Save(Event e)
        {
            e.CreateTime = DateTime.Now;

            using (DbConnection conn = SqlHelper.Connection)
            {
                string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText =
                    @"INSERT INTO t_Events
      (UserId, [CreateTime], IP, [Action], Description)
VALUES (?,?,?,?,?);";

                DbProviderFactory factory
              = DbProviderFactories.GetFactory(providerName);
                DbParameter parameter1 = factory.CreateParameter();
                parameter1.Value = e.UserId;

                DbParameter parameter2 = factory.CreateParameter();
                parameter2.Value = e.CreateTime.ToString();

                DbParameter parameter3 = factory.CreateParameter();
                parameter3.Value = e.Action;

                DbParameter parameter4 = factory.CreateParameter();
                parameter4.Value = e.Action;

                DbParameter parameter5 = factory.CreateParameter();
                parameter5.Value = e.Description;

                cmd.Parameters.AddRange(
                new DbParameter[] 
                { 
                    parameter1, parameter2, parameter3, 
                    parameter4, parameter5 
                }
                );
                conn.Open();

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY;";

                int eventId = (int)cmd.ExecuteScalar();

                conn.Close();

                //todo:...
                e.EventId = Guid.NewGuid();

                return e;
            }
        }
    }
}